﻿using System;
using System.Data;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

using DataHelper;


namespace DAL
{
  public  class InOutData
    {

        public  void ImportTxtToDB(string connstr, string tablename)
        {

            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "文本文件(*.txt)|*.txt";
            openFileDialog.Title = "选择文本文件";

            if (DialogResult.Cancel == openFileDialog.ShowDialog())
            {
                return;
            }

            string filename = openFileDialog.FileName;
            SqlConnection conn = new SqlConnection(connstr);
            conn.Open();
            //打开文件并显示其内容 
            StreamReader reader = null;
            try
            {
                reader = new StreamReader(filename);

                string Titlestr = "";

                int linenum = 0;
                DataTable dt=null;
                for (string line = reader.ReadLine(); line != null; line = reader.ReadLine())
                {
                    if (linenum == 0)
                    {
                        Titlestr = line;
                        if (!TableIsExists( tablename))
                            MakeDbTable(tablename, line);   //创建表;
                        dt =TableInfo.GetTableStruct(tablename);
                    }
                    else
                    {
                        InsertToTable( tablename, line, Titlestr,dt);   //表数据插入;
                    }
                    linenum++;
                }
                MessageBox.Show("导入完成");
            }
            catch (IOException e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                if (reader != null)
                    reader.Close();
                conn.Close();
            }
        }

        public void ImportExcelToDB(string connstr, string tablename)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "Microsoft Excel 文件(*.xls)|*.xls";
            openFileDialog.Title = "选择Excel文件";

            if (DialogResult.Cancel == openFileDialog.ShowDialog())
            {
                return;
            }

            string ExcelPath = openFileDialog.FileName;
            //  txtExcelPath.Text = ExcelPath;

            string ConnectionExcel = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + ExcelPath + ";" + "Extended Properties=Excel 8.0;";

            OleDbConnection conn = new OleDbConnection(ConnectionExcel);
            conn.Open();
            System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);


            string EtableName = schemaTable.Rows[0][2].ToString().Trim(new char[] { '$', '\'' });

            OleDbDataAdapter adp = new OleDbDataAdapter("Select * from  [" + EtableName + "$]", conn);
            DataSet ds = new DataSet();
            adp.Fill(ds, "Book1");

            if (InsertSQLServer(ds, tablename, connstr))
            {
                MessageBox.Show("导入表数据完成");
            }
            else MessageBox.Show("导入表数据失败");
        }

        public void ImportAccessToDB(string connstr, string tablename)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "Microsoft Access 文件(*.mdb)|*.mdb";
            openFileDialog.Title = "选择Access文件";

            if (DialogResult.Cancel == openFileDialog.ShowDialog())
            {
                return;
            }

            string AccessPath = openFileDialog.FileName;


            string ConnectionAccess = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + AccessPath + ";";
            try
            {
                OleDbConnection conn = new OleDbConnection(ConnectionAccess);
                conn.Open();

                string Atablename = "";
                DataTable dt = conn.GetSchema("Tables");//获取ACCESS数据库中所有的表\查询\宏\窗体\模块
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (dt.Rows[i].ItemArray[3].ToString() == "TABLE")//判断是否是用户表
                    {
                        Atablename = dt.Rows[i].ItemArray[2].ToString();

                    }
                }

                OleDbDataAdapter adp = new OleDbDataAdapter("Select * from  " + Atablename, conn);
                DataSet ds = new DataSet();
                adp.Fill(ds, "Book1");

                if (InsertSQLServer(ds, tablename, connstr))
                    MessageBox.Show("导入表数据完成");
                else MessageBox.Show("导入表数据失败");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        public  void MakeDbTable( string tablename, string linestr)  //在数据库中建表;
        {

            try
            {
                string commtxt = "CREATE TABLE " + tablename + "(";

                string[] tmpstrs = linestr.Split('\t');
                int i = 0;
                foreach (string tmpstr in tmpstrs)
                {
                    if (i != 0)
                        commtxt += ",";
                    commtxt += tmpstr;
                    commtxt += " varchar(50)";
                    i++;
                }

                commtxt += " )";

                SQLHelper.ExecuteSql(commtxt);
               
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.ToString());
 
            }
        }

        public  void ExportSqlToTxt(string Sqlstr, string connstr)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "TXT files (*.txt)|*.txt";
            saveFileDialog.FilterIndex = 0;
            saveFileDialog.RestoreDirectory = true;
            saveFileDialog.CreatePrompt = true;
            saveFileDialog.Title = "导出TXT文件到";

            if (saveFileDialog.ShowDialog() == DialogResult.Cancel)
            {
                return;
            }


            DataSet ds =SQLHelper.ExecuteSqlGetDataSet(Sqlstr);  //获取SQL语句的结果集

            Stream myStream;
            myStream = saveFileDialog.OpenFile();
            StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));
            string str = "";
            try
            {
                //写标题  
                for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                {
                    if (i > 0)
                    {
                        str += "\t";
                    }
                    str += ds.Tables[0].Columns[i].ColumnName;
                }

                sw.WriteLine(str);
                //写内容 
                for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
                {
                    string tempStr = "";
                    for (int k = 0; k < ds.Tables[0].Columns.Count; k++)
                    {
                        if (k > 0)
                        {
                            tempStr += "\t";
                        }
                        if (ds.Tables[0].Rows[j][k] != null)
                            tempStr += ds.Tables[0].Rows[j][k].ToString();
                    }
                    sw.WriteLine(tempStr);
                }
                sw.Close();
                myStream.Close();
                MessageBox.Show("完成导出");
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
            }
            finally
            {
                sw.Close();
                myStream.Close();
            }
        }

        public  void ExportSqlToExcel(string SqlStr, string connstr)
        {

            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
            saveFileDialog.FilterIndex = 0;
            saveFileDialog.RestoreDirectory = true;
            saveFileDialog.CreatePrompt = true;
            saveFileDialog.Title = "导出Excel文件到";
            if (saveFileDialog.ShowDialog() != DialogResult.OK)
                return;

            DataSet ds = SQLHelper.ExecuteSqlGetDataSet(SqlStr);

            string FileName = saveFileDialog.FileName;

            DeleteFile(FileName); //文件已存在,则删除;

            OleDbParameter[] parm = new OleDbParameter[ds.Tables[0].Columns.Count];

            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FileName + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(connString);
            conn.Open();

            OleDbCommand objCmd = new OleDbCommand();
            objCmd.Connection = conn;
            //建立表结构   

            string CommTitleTxt = "";

            CommTitleTxt = "CREATE   TABLE   Sheet1(";

            //写标题  
            for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
            {
                if (i > 0)
                {
                    CommTitleTxt += " varchar,";
                }
                CommTitleTxt +="["+ ds.Tables[0].Columns[i].ColumnName+"]";
            }

            CommTitleTxt += " varchar)";


            objCmd.CommandText = CommTitleTxt;
            objCmd.ExecuteNonQuery();
            //建立插入动作的Command   

            string CommContextstr = "";
            CommContextstr = "INSERT   INTO   Sheet1(";
            for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
            {
                if (j != 0)
                    CommContextstr += ",";
                CommContextstr += "[" + ds.Tables[0].Columns[j].ColumnName + "]";
            }

            CommContextstr += ")";

            CommContextstr += " Values(";

            for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
            {
                if (j != 0)
                    CommContextstr += ",";
                CommContextstr += "@" + ds.Tables[0].Columns[j].ColumnName;
            }

            CommContextstr += ")";

            objCmd.CommandText = CommContextstr;

            for (int k = 0; k < ds.Tables[0].Columns.Count; k++)
            {
                parm[k] = new OleDbParameter("@" + ds.Tables[0].Columns[k].ColumnName, OleDbType.VarChar);
                objCmd.Parameters.Add(parm[k]);
            }

            ////遍历数据集DS将数据插入新建的Excel文件中   
            for (int j = 0; j < ds.Tables[0].Rows.Count - 1; j++)
            {
                for (int k = 0; k < ds.Tables[0].Columns.Count; k++)
                {
                    if (ds.Tables[0].Rows[j][k] != null)
                        parm[k].Value = ds.Tables[0].Rows[j][k].ToString();
                }
                objCmd.ExecuteNonQuery();
                System.Windows.Forms.Application.DoEvents();
            }

            conn.Close();
            MessageBox.Show("完成导出");
        }

        public  void DeleteFile(string strPath)
        {
            if (File.Exists(strPath))
            {
                File.Delete(strPath);
            }
        }

        public  void ExportSqlToAccess(string SqlStr, string connstr)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "Access files (*.mdb)|*.mdb";
            saveFileDialog.FilterIndex = 0;
            saveFileDialog.RestoreDirectory = true;
            saveFileDialog.CreatePrompt = true;
            saveFileDialog.Title = "导出Access文件到";

            if (saveFileDialog.ShowDialog() == DialogResult.Cancel)
            {
                return;
            }

            DataSet ds =SQLHelper.ExecuteSqlGetDataSet(SqlStr);



            string AccessPath = saveFileDialog.FileName;
            DeleteFile(AccessPath); //文件已存在,则删除;


            ADOX.Catalog cat = new ADOX.Catalog();
            cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + AccessPath + ";");  //创建数据库文件
            MessageBox.Show("数据库：" + AccessPath + "已经创建成功！");

            OleDbParameter[] parm = new OleDbParameter[ds.Tables[0].Columns.Count];

            string ConnectionAccess = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + AccessPath + ";";

            OleDbConnection conn = new OleDbConnection(ConnectionAccess);
            conn.Open();

            OleDbCommand objCmd = new OleDbCommand();
            objCmd.Connection = conn;
            //建立表结构   

            string CommTitleTxt = "";



            CommTitleTxt = "CREATE   TABLE   Table1(";

            //写标题  
            for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
            {
                if (i > 0)
                {
                    CommTitleTxt += " varchar,";
                }
                CommTitleTxt += "["+ds.Tables[0].Columns[i].ColumnName+"]";
            }

            CommTitleTxt += " varchar)";


            objCmd.CommandText = CommTitleTxt;
            objCmd.ExecuteNonQuery();
            //建立插入动作的Command   

            string CommContextstr = "";
            CommContextstr = "INSERT   INTO   Table1(";
            for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
            {
                if (j != 0)
                    CommContextstr += ",";
                CommContextstr += "["+ds.Tables[0].Columns[j].ColumnName+"]";
            }

            CommContextstr += ")";

            CommContextstr += " Values(";

            for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
            {
                if (j != 0)
                    CommContextstr += ",";
                CommContextstr += "@" + ds.Tables[0].Columns[j].ColumnName;
            }

            CommContextstr += ")";

            objCmd.CommandText = CommContextstr;

            for (int k = 0; k < ds.Tables[0].Columns.Count; k++)
            {
                parm[k] = new OleDbParameter("@" + ds.Tables[0].Columns[k].ColumnName, OleDbType.VarChar);
                objCmd.Parameters.Add(parm[k]);
            }

            ////遍历DataGridView将数据插入新建的Access文件中   
            for (int j = 0; j < ds.Tables[0].Rows.Count - 1; j++)
            {
                for (int k = 0; k < ds.Tables[0].Columns.Count; k++)
                {
                    if (ds.Tables[0].Rows[j][k] != null)
                        parm[k].Value = ds.Tables[0].Rows[j][k].ToString();
                }
                objCmd.ExecuteNonQuery();
                System.Windows.Forms.Application.DoEvents();
            }
            objCmd.Dispose();
            conn.Close();
            conn.Dispose();
            MessageBox.Show("完成导出");

        }

       
       


        public  void InsertToTable(string tablename, string linestr, string Titlestr,DataTable dt)  //在表中插数据;
        {
            

            string[] tmpstrs2 = linestr.Split('\t');
            if (tmpstrs2.Length == 0)  //空数据直接返回;
                return;

            string[] fType = new string[tmpstrs2.Length];//字段类型
            int[] fLength = new int[tmpstrs2.Length]; //长度

            string commtxt = "Insert into " + tablename + "(";
            string[] tmpstrs = Titlestr.Split('\t');
            int i = 0;
            foreach (string tmpstr in tmpstrs)
            {
                for (int t = 0; t < dt.Rows.Count; t++)
                {
                    if ("["+dt.Rows[t]["col_name"].ToString().Trim()+"]" == tmpstr)
                    {
                        fType[i] = dt.Rows[t]["col_type"].ToString();
                        fLength[i] = Int32.Parse(dt.Rows[t]["col_len"].ToString());
                       
                    }
                }
               
                
                if (i != 0)
                    commtxt += ",";
                commtxt += tmpstr;
                i++;
            }
            
            commtxt += ") Values(";


            int j = 0;
            foreach (string tmpstr2 in tmpstrs2)
            {
                if (j != 0)
                    commtxt += ",";
                if (fType[j] == "int" || fType[j] == "float" || fType[j] == "tinyint" || fType[j] == "decimal" || fType[j] == "bit" || fType[j] == "bigint" || fType[j] == "smallint" || fType[j] == "money" || fType[j] == "smallmoney" || fType[j] == "real" || fType[j] == "numeric")
                    commtxt += tmpstr2;

                else if (fType[j] == "datetime" || fType[j] == "smalldatetime" || fType[j] == "timestamp")
                {
                    commtxt += "'" + tmpstr2 + "'";
                }
                else
                {
                    if (tmpstr2.Length > fLength[j])
                        commtxt += "'" + tmpstr2.Substring(0, fLength[j]) + "'";
                    else
                        commtxt += "'" + tmpstr2 + "'";
                }
                //不支持二进制，图片，XML等数据的导入
                j++;
            }

            commtxt += ")";

            SQLHelper.ExecuteSql(commtxt);
           
            
        }

        public  bool TableIsExists(string tablename)  //表是否已经存在;
        {
            try
            {
                string SqlStr = "SELECT name FROM sysobjects WHERE type='U'and name ='" + tablename + "'";
                int iResult = 0;
                iResult =Int32.Parse(SQLHelper.ExecuteSqlGetNumber(SqlStr));
                if (iResult == 0)
                    return false;
                else return true;
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.ToString());
                return true;
            }
        }

        public bool InsertSQLServer(DataSet ds, string tablename, string connstr)
        {
            try
            {
                SqlConnection con = new SqlConnection(connstr);//创建连接
                con.Open();

                string Titlestr = "";
                for (int i = 0; i < ds.Tables[0].Columns.Count; i++) //取字段名;
                {
                    if (i != 0)
                        Titlestr += "\t";  //tab字符分隔;
                    Titlestr += "["+ds.Tables[0].Columns[i].ColumnName+"]";
                }

                if (!TableIsExists( tablename))   //表不存在创建表;
                {
                    MakeDbTable(tablename, Titlestr);
                }


                DataTable dt=TableInfo.GetTableStruct(tablename);

              
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string linestr = "";
                    for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                    {
                        if (j != 0)
                            linestr += "\t";    //Tab字符分隔;
                        if (ds.Tables[0].Rows[i][j] != null)
                            linestr += ds.Tables[0].Rows[i][j].ToString();
                        else linestr += " ";
                    }
                    InsertToTable(tablename, linestr, Titlestr,dt);//插入数据
                }
                con.Close();
                return true;
            }
            catch
            {
                return false;
            }

        }




       

        

        ////class end


    }
}
